/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Utilidades;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
 *
 * @author diogo
 */
public class UtilityDB {

	public static final boolean _MYSQL = false;
	public static final boolean _POSTGRES = true;
	public static String[] divisor = {"(", ",", ")"};
	public String tableName;

	public UtilityDB(String tableName) {
		this.tableName = tableName;
	}
	private static final String INSERT_MASK = "INSERT INTO %s (%s) VALUES %s";
	private static final String DELETE_MASK = "DELETE FROM %s";
	private static final String SELECT_MASK = "SELECT * FROM %s";
	private static final String UPDATE_MASK = "UPDATE %s SET ";
	public static final int EQUAL = 0;
	public static final int STARTS_WITH = 1;
	public static final int ENDS_WITH = 2;
	public static final int CONTAINS = 3;

	/**
	 * this method creates an INSERT instruction.
	 * if call is: <i>UtilityDB.CreateInsert("user", "name","Name","age",123))</i> should return
	 * this string: "INSERT (name,age) INTO user VALUES ('Name',123)"
	 * @param tableName - the name of table
	 * @param values - the values to insert
	 * @return string as something like this: "INSERT INTO table VALUES args"
	 */
	public static String CreateInsert(String tableName, Object... values) {
		return new UtilityDB(tableName).createInsert(values);
	}

	/**
	 * this method creates an INSERT instruction based on object's table name.
	 * if call is: <i>user.createInsert("name","Name","age",123))</i> should return
	 * this string: "INSERT (name,age) INTO user VALUES ('Name',123)"
	 * @param values - the values to insert
	 * @return string as something like this: "INSERT INTO table VALUES args"
	 */
	public String createInsert(Object... args) {
		Object[] keys = null;
		Object[] values = null;
		String key = "";
		if (args.length % 2 != 0) {
			System.err.println("Value of column \"" + args[args.length - 1] + "\" is missing!");
			return "fail sql";
		}
		keys = new Object[args.length / 2];
		values = new Object[args.length / 2];
		int count = 0;
		for (int x = 0; x < args.length; x += 2) {
			keys[count] = args[x];
			values[count++] = args[x + 1];
			key += args[x].toString();
			if (x + 2 < args.length) {
				key += ",";
			}
		}

		return String.format(INSERT_MASK, tableName, key, FormatValues(values));
	}

	/**
	 * this method creates a DELETE instruction.
	 * if call is: UtilityDB.CreateDelete("user", "id", 9)); should return
	 * this string: "DELETE FROM user WHERE id = 9"
	 * @param tableName - the name of table
	 * @param values - the values to insert
	 * @return string as something like this: "DELETE FROM table args..."
	 */
	public static String CreateDelete(String tableName, Object... values) {
		return new UtilityDB(tableName).createDelete(values);
	}

	/**
	 * this method creates a DELETE instruction based on object's table name.
	 * if call is: user.createDelete("id", 9, "name","foo")); should return
	 * this string: "DELETE FROM user WHERE id = 9 AND name LIKE 'foo'"
	 * @param values - the values to insert
	 * @return string as something like this: "DELETE FROM table args..."
	 */
	public String createDelete(Object... values) {
		String output = String.format(DELETE_MASK, tableName);
		return output + createComparisons(values);
	}

	/**
	 * this method creates a SELECT instruction.
	 * if call is: UtilityDB.CreateSelect("user","id", 9, "name","foo")); should return
	 * this string: "SELECT * FROM user WHERE id = 9 AND name LIKE 'foo'"
	 *
	 * @param tableName - the name of table
	 * @param values - the values to select
	 * @return string as something like this: "SELECT * FROM table args..."
	 */
	public static String CreateSelect(String tableName, Object... values) {
		return new UtilityDB(tableName).createSelect(values);
	}

	/**
	 * this method creates a SELECT instruction based on object's table name.
	 * if call is: user.CreateSelect("id", 9, "name","foo")); should return
	 * this string: "SELECT * FROM user WHERE id = 9 AND name LIKE 'foo'"
	 *
	 * @param values - the values to select
	 * @return string as something like this: "SELECT * FROM table args..."
	 */
	public String createSelect(Object... values) {
		String output = String.format(SELECT_MASK, tableName);
		return output + createComparisons(values);
	}

	public static String CreateUpdate(String tableName, Object... values) {
		return new UtilityDB(tableName).createUpdate(values);
	}

	public String createUpdate(Object... args) {
		String output = String.format(UPDATE_MASK, tableName);

		Object[] keys = null;
		Object[] values = null;
		String fields = " ";
		if (args.length % 2 != 0) {
			System.err.println("Value of column \"" + args[args.length - 1] + "\" is missing!");
			return "fail sql";
		}
		keys = new Object[args.length / 2];
		values = new Object[args.length / 2];
		for (int x = 0; x < args.length; x += 2) {
			int count = x / 2;

			keys[count] = args[x];
			values[count] = args[x + 1];
			fields += String.format(" %s = %s", keys[count].toString(), FormatSingleObject(values[count].toString()));
			if (x + 2 < args.length) {
				fields += ",";
			}
		}


		return output + fields + " ";
	}

	public static String CreateComparisons(String tableName, Object... values) {
		return new UtilityDB(tableName).createComparisons(values);
	}

	public String createComparisons(Object... values) {
		String output = "";
		if (values.length % 2 != 0) {
			System.err.println("Value of column \"" + values[values.length - 1] + "\" is missing!");
			return "fail sql";
		}
		if (values.length <= 0) {
			return output;
		}
		output += " WHERE ";
		for (int x = 0; x < values.length; x += 2) {
			output += UtilityDB.Compare(values[x].toString().replace(" ", ""), values[x + 1]);
			if (x + 2 < values.length) {
				output += " AND ";
			}
		}
		return output;
	}

	public static String Compare(String column, Object value) {
		return Compare(column, value, true, 0);
	}

	/**
	 * 
	 * @param column column name
	 * @param value the value to compare (anything, like integer, date, string)
	 * @param caseSensitive false to ignore case.
	 * @param type if is EQUAL, then does not uses LIKE.
	 * @return
	 */
	public static String Compare(String column, Object value, boolean caseSensitive, int type) {
		String output = column;
		if (value instanceof String) {
			String temp = value.toString();
			if (type == STARTS_WITH) {
				temp += "%";
			} else if (type == ENDS_WITH) {
				temp = "%" + value;
			} else if (type == CONTAINS) {
				temp = "%" + value + "%";
			}
			output = String.format("%s(%s) %s %s(%s)", (caseSensitive ? "" : "LOWER"), column, (type == EQUAL ? "=" : "LIKE"), (caseSensitive ? "" : "LOWER"), FormatSingleObject(temp));


		} else if (value instanceof Number || value instanceof Boolean) {
			output += " = " + FormatSingleObject(value);
		}
		//output += FormatSingleObject(value);
		return output;
	}
	/*
	public static String formatValues(Object... values) {
	return formatValues("(,)", values);
	}
	 */

	public static String FormatValues(Object... values) {
		/*
		System.out.println("patternn = " + pattern);
		 *
		 */
		return FormatValues(divisor, values);

	}

	private static String FormatValues(String[] divisors, Object... values) {
		String begin = "" + divisors[0];
		String separator = "" + divisors[1];
		String end = "" + divisors[2];
		String output = begin;


		int limit = values.length - 1;
		for (int x = 0; x < values.length; x++) {
			output += FormatSingleObject(values[x]) + (x < limit ? separator : "");
		}
		output += end;
		return output;
	}

	/**
	 * formats a single object into a DB format
	 * i.e.: if is string, returns "'string'", if number "999"
	 *
	 * @return
	 */
	public static String FormatSingleObject(Object o) {
		if (o instanceof Number || o instanceof Boolean) {
			return "" + o;
		} else if (o instanceof String) {
			return String.format("'%s'", o.toString().replace("'", "''"));
		} else if (o instanceof Date) {
			if (_POSTGRES) {
				return String.format("'%s'::date", UtilityTime.formatDate((Date) o, "yyyy/MM/dd"));
			}
			return "Date format is not implemnted yet!";
		} else if (o instanceof int[]) {
			// convert integers to double to use at FormatArray.
			int[] tempInt = (int[]) o;
			double[] temp = new double[tempInt.length];
			for (int i = 0; i < temp.length; i++) {
				temp[i] = (double) tempInt[i];

			}
			return FormatArray(temp, 0);
		} else if (o instanceof float[] || o instanceof double[]) {
			return FormatArray((double[]) o, 2);
		} else if (o == null) {
			return "null";
		}

		return "undefined type";
	}

	/**
	 * 
	 * @param array the values to format.
	 * @param precision amount of number after the dot. e.g.: 0 = "1,2,3"; 1 = "1.0, 2.0, 3.0"
	 * @return something like this: ARRAY[1,2,3]
	 */
	public static String FormatArray(double[] array, int precision) {
		StringBuilder output = new StringBuilder("");

		// has anything in the array?
		if (array.length > 0) {
			// yes, then format it.

			// precision must be higher or equal 0
			precision = Math.max(0, precision);

			String mask = String.format("%%.%df", precision);

			output.append("ARRAY[");
			for (double d : array) {
				output.append(String.format(mask + ", ", d));
			}
			//System.out.println(output);

			// removind the last comma (,)
			//output = output.deleteCharAt(output.length() -2);
			int length = output.length();
			output = output.delete(length - 2, length);
			output.append("]");
		} else {
			output.append("empty array!");
		}

		return output.toString();
	}

	public static void SetDivisor(String[] divisor) {

		if (divisor.length != 3) {
			System.err.println("Couldn't set divisor \"" + divisor + "\"! must have 3 Strings");
		} else {
			UtilityDB.divisor = divisor;
		}
	}

	public static int GetRowAmount(ResultSet rs) throws SQLException {
		int amount = 0;
		rs.last();
		amount = rs.getRow();
		rs.beforeFirst();
		return amount;
	}

	/**
	 * function to create a SQL function call.
	 * @param functionName the name of function defined in the database
	 * @param args all the parameters for the specified function
	 * @return SQL ready to call. e.g.: "SELECT * FROM function_name('text', 999)"
	 */
	public static String CallFunction(String functionName, Object... args) {
		StringBuilder output = new StringBuilder("");

		// parameters contains table name too
		StringBuilder parameters = new StringBuilder(functionName);
		if (args.length > 0) {
			parameters.append(" ").append(UtilityDB.FormatValues(args));

		}
		output.append(String.format(SELECT_MASK, parameters));
		return output.toString();
	}
/*

	public static void main(String[] args) {
//        System.out.println(CreateInsert("teste", "dia",new Date()));
//		System.out.println(UtilityDB.CreateComparisons("tabela", "nome", "asd"));
//		System.out.println(UtilityDB.Compare("tabela", "nome"));
//		System.out.println(UtilityDB.CreateInsert("tabela", "nome", "aaa", "asdddd", "dddssaa"));
//		System.out.println(UtilityDB.FormatValues("aaaa", 999));
		//System.out.println(UtilityDB.CallFunction("nomeFuncao", "aaaaa", 9999));
//		System.out.println(UtilityDB.CallFunction("nomeFuncao"));
		//double[] i = {1,2,3};
		//System.out.println(FormatArray(i, 0));
//		System.out.println(String.format("%.0f", 12.3456789));
		//System.out.println(String.format("%d", 12));
		//System.out.println(CallFunction("", new double[]{2,3}, new int[]{1,2,3}));
		System.out.println(CreateSelect("a") + " WHERE " + Compare("logi", "login do usario", true, EQUAL));
	}
	//*/
}
